Hi,
I've been using Postgres for a while, almost exclusively through the
perl DBI (although I do plenty of work on the command line).
I have realized, belatedly, that I need transactions for this thing I
want to accomplish, but I've not done transactions before, so I need
a bit of help. And, I'm not sure whether it's a transaction I need,
or a lock.
I have (many) tables with automatically entering serial value as
primary key, set by a sequence. I need to insert a row, and then get
the value of that row I just entered. I thought first of doing two
sql statements in a row:
if the primary key is table_id, with default value
"nextval('table_seq') - then these two statements:
insert into table (field1,field2,field3) values (value1,value2,value3)
select currval('table_seq')
work to get me the value I need. Except, of course if someone else
has inserted a row inbetween these two statements.
I tried a transaction test, and this is what I got:
pew=# begin work;
BEGIN
pew=# insert into categories values
('23423423','test','testing','3','today','today','mpm','test
category');
INSERT 83910 1
pew=# select currval('category_id');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
pew=# commit work
pew-# ;
COMMIT
pew=# select * from categories;
And the insert didn't happen.
Am I thinking about this right? Is there a better way to get the
value of a newly inserted record?
Thanks!
PS: I'm subscribed to sql, odbc and general, and have not been
getting general mail for quite some time. I've send emails to the
address that's supposed to be read by humans, but gotten no response.
If anyone is in a position to help me out - much appreciated!
--
.Michelle
--------------------------
Michelle Murrain, Technology Consulting
tech@murrain.net http://www.murrain.net
413-253-2874 ph
413-222-6350 cell
413-825-0288 fax
AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575
"A vocation is where the world's hunger & your great gladness meet."
